﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Data;

namespace FreeProperty.Data
{
    public class PropertyDesc : NameValue
    {
        public SqlGuid ID { get; set; }
        public SqlInt32 SerialNumber { get; set; }
        public SqlInt32 DistrictID { get; set; }
        public string Location { get; set; }
        public string Address { get; set; }
        public string PropertyDescription { get; set; }
        public string Name { get; set; }
        public string ContactNo { get; set; }
        public string PreferredTime { get; set; }
        public string EmailId { get; set; }
        public FreePropertyType PropertyType { get; set; }
        public FreePropertyUsageType PurposeType { get; set; }
        public string PropertyTypeStr { get; set; }
        public string PurposeTypeStr { get; set; }
        public SqlInt32 Price { get; set; }
        public SqlInt32 PriceMin { get; set; }
        public SqlInt32 PriceMax { get; set; }
        public SqlBoolean Inactive { get; set; }
        public SqlGuid UserID { get; set; }
        public SqlGuid Save()
        {
            try
            {
                SqlCommand cmd = GetSPCommand("saveProperty");
                cmd.Parameters.Add(new SqlParameter("@prmID", ID));
                cmd.Parameters.Add(new SqlParameter("@prmDistrictID", DistrictID));
                cmd.Parameters.Add(new SqlParameter("@prmLocation", Location));
                cmd.Parameters.Add(new SqlParameter("@prmAddress", Address));
                cmd.Parameters.Add(new SqlParameter("@prmPropertyDesc", PropertyDescription));
                cmd.Parameters.Add(new SqlParameter("@prmName", Name));
                cmd.Parameters.Add(new SqlParameter("@prmContactNo", ContactNo));
                cmd.Parameters.Add(new SqlParameter("@prmPreferredTime", PreferredTime));
                cmd.Parameters.Add(new SqlParameter("@prmPropertyType", PropertyType));
                cmd.Parameters.Add(new SqlParameter("@prmPurposeType", PurposeType));
                cmd.Parameters.Add(new SqlParameter("@prmPrice", Price));
                cmd.Parameters.Add(new SqlParameter("@prmUserID", UserID));
                cmd.Parameters.Add(new SqlParameter("@prmEmailId", EmailId));
                
                cmd.ExecuteNonQuery();

            }
            finally
            {
                CloseConnection();
            }

            return ID;
        }
        public void ChangePropertyStatus()
        {
            try
            {
                SqlCommand cmd = GetSPCommand("changePropertyStatus");
                cmd.Parameters.Add(new SqlParameter("@prmID", ID));
                cmd.Parameters.Add(new SqlParameter("@prmStatus", Inactive));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }
        public List<PropertyDesc> LoadUserProperties(SqlGuid UserId,PagerData oPagerData)
        {
            SqlCommand cmd = GetSPCommand("getUserProperties");

            cmd.Parameters.Add(new SqlParameter("@prmUserID", UserId));
            cmd.Parameters.Add(new SqlParameter("@prmLocation", Location));
            cmd.Parameters.Add(new SqlParameter("@prmStatus", Inactive));
            cmd.Parameters.AddWithValue("@PageNumber", oPagerData.PageNumber);
            cmd.Parameters.AddWithValue("@PageSize", oPagerData.PageSize);
            SqlParameter param = cmd.Parameters.AddWithValue("@TotalPages", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.AddWithValue("@FirstItem", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.AddWithValue("@LastItem", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.AddWithValue("@TotalItems", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            List<PropertyDesc> lstPropertyDesc = new List<PropertyDesc>();
            while (reader.Read())
            {
                PropertyDesc oPropertyDesc = new PropertyDesc();
                oPropertyDesc.SerialNumber = ToSqlInt32(reader["ID"]);
                oPropertyDesc.ID = ToSqlGuid(reader["PropertyID"]);
                oPropertyDesc.Location = ToString(reader["Location"]);
                oPropertyDesc.Address = ToString(reader["Address"]);
                oPropertyDesc.PropertyDescription = ToString(reader["PropertyDescription"]);
                oPropertyDesc.Name = ToString(reader["Name"]);
                oPropertyDesc.ContactNo = ToString(reader["ContactNo"]);
                oPropertyDesc.PreferredTime = ToString(reader["PreferredTime"]);
                oPropertyDesc.EmailId = ToString(reader["EmailID"]);
                oPropertyDesc.Price = ToSqlInt32(reader["Price"]);

                oPropertyDesc.PropertyType = (FreePropertyType)ToSqlInt32(reader["PropertyType"]).Value;
                oPropertyDesc.PurposeType = (FreePropertyUsageType)ToSqlInt32(reader["PurposeType"]).Value;
                oPropertyDesc.PropertyTypeStr = oPropertyDesc.PropertyType.ToString();
                oPropertyDesc.PurposeTypeStr = oPropertyDesc.PurposeType.ToString();
                lstPropertyDesc.Add(oPropertyDesc);
            }
            reader.Close();
            reader = null;
            oPagerData.TotalPages = (int)cmd.Parameters["@TotalPages"].Value;
            oPagerData.TotalItems = (int)cmd.Parameters["@TotalItems"].Value;
            oPagerData.FirstItem = (int)cmd.Parameters["@FirstItem"].Value;
            oPagerData.LastItem = (int)cmd.Parameters["@LastItem"].Value;
            return lstPropertyDesc;
        }
        public List<PropertyDesc> LoadAll(PagerData oPagerData)
        {
            SqlCommand cmd = GetSPCommand("SearchProperties");

            cmd.Parameters.Add(new SqlParameter("@prmDistrictID", DistrictID));
            cmd.Parameters.Add(new SqlParameter("@prmLocation", Location));
            cmd.Parameters.Add(new SqlParameter("@prmPropertyType", PropertyType));
            cmd.Parameters.Add(new SqlParameter("@prmPurposeType", PurposeType));
            cmd.Parameters.Add(new SqlParameter("@prmPriceMin", PriceMin));
            cmd.Parameters.Add(new SqlParameter("@prmPriceMax", PriceMax));

            cmd.Parameters.AddWithValue("@PageNumber", oPagerData.PageNumber);
            cmd.Parameters.AddWithValue("@PageSize", oPagerData.PageSize);
            SqlParameter param = cmd.Parameters.AddWithValue("@TotalPages", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.AddWithValue("@FirstItem", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.AddWithValue("@LastItem", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.AddWithValue("@TotalItems", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            List<PropertyDesc> lstPropertyDesc = new List<PropertyDesc>();
            while (reader.Read())
            {
                PropertyDesc oPropertyDesc = new PropertyDesc();
                oPropertyDesc.ID = ToSqlGuid(reader["PropertyID"]);
                oPropertyDesc.Location = ToString(reader["Location"]);
                oPropertyDesc.Address = ToString(reader["Address"]);
                oPropertyDesc.PropertyDescription = ToString(reader["PropertyDescription"]);
                oPropertyDesc.Name = ToString(reader["Name"]);
                oPropertyDesc.ContactNo = ToString(reader["ContactNo"]);
                oPropertyDesc.PreferredTime = ToString(reader["PreferredTime"]);
                oPropertyDesc.EmailId = ToString(reader["EmailID"]);
                oPropertyDesc.Price = ToSqlInt32(reader["Price"]);
                oPropertyDesc.SerialNumber = ToSqlInt32(reader["ID"]);
                lstPropertyDesc.Add(oPropertyDesc);
            }
            reader.Close();
            reader = null;
            oPagerData.TotalPages = (int)cmd.Parameters["@TotalPages"].Value;
            oPagerData.TotalItems = (int)cmd.Parameters["@TotalItems"].Value;
            oPagerData.FirstItem = (int)cmd.Parameters["@FirstItem"].Value;
            oPagerData.LastItem = (int)cmd.Parameters["@LastItem"].Value;
            return lstPropertyDesc;
        }
        public void LoadProperty()
        {
            SqlCommand cmd = GetSPCommand("getPropertyByID");

            cmd.Parameters.Add(new SqlParameter("@prmID", ID));
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.Read())
            {
                ID = ToSqlGuid(reader["ID"]);
                DistrictID = ToSqlInt32(reader["DistrictID"]);
                StateID = ToSqlInt32(reader["StateID"]);
                Location = ToString(reader["Location"]);
                Address = ToString(reader["Address"]);
                PropertyDescription = ToString(reader["PropertyDescription"]);
                Name = ToString(reader["Name"]);
                EmailId = ToString(reader["EmailID"]);
                ContactNo = ToString(reader["ContactNo"]);
                PreferredTime = ToString(reader["PreferredTime"]);
                PropertyType = (FreePropertyType)ToSqlInt32(reader["PropertyType"]).Value;
                PurposeType = (FreePropertyUsageType)ToSqlInt32(reader["PurposeType"]).Value;
                Price = ToSqlInt32(reader["Price"]);
                Inactive = ToSqlBoolean(reader["Inactive"]);
                UserID = ToSqlGuid(reader["UserID"]);
            }
            reader.Close();
            reader = null;
            
        }
        public List<string> GetLocationForDostrict()
        {
            SqlCommand cmd = GetSPCommand("getDistrictLocations");

            cmd.Parameters.Add(new SqlParameter("@prmDistrictID", DistrictID));
            cmd.Parameters.Add(new SqlParameter("@prmName", Location));
            
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            List<string> lstLocations = new List<string>();
            while (reader.Read())
            {
                lstLocations.Add(ToString(reader["Name"]));
            }
            reader.Close();
            reader = null;
            
            return lstLocations;
        }



        public SqlInt32 StateID { get; set; }
    }
    public enum FreePropertyType
    {
        Residential = 1,
        Commercial
    }
    public enum FreePropertyUsageType
    {
        Buy = 1,
        Sale,
        ForRent,
        ToRent
    }
    public class PagerData
    {
        public int PageNumber { get;set ;}
        public int PageSize { get;set ;}
        public int TotalPages { get;set ;}
        public int TotalItems { get;set ;}
        public int FirstItem { get;set ;}
        public int LastItem { get;set ;}
    }
}
